In recent years, business intelligence tools have become increasingly utilized by large business enterprises and other organizations. Business intelligence provides current and historical views of business operations by analyzing internal, structured data and business processes of the organization. It is often used to create future models and predictions in order to support better business decision making. As such, business intelligence tools can lead to decreased costs and increased efficiency, productivity and profit margins for many companies.
Business intelligence is usually implemented as software and/or hardware tools that are used to collect and analyze data and to transform the raw data into meaningful and useful information used to enable more effective strategic, tactical, and operational insights and decision-making. As such, a typical business intelligence server relies on data that may reside in a variety of places, including but not limited to databases, repositories, content management systems, application servers and many other sources.
In a typical business intelligence server, data is collected from all (or some) of these sources and placed into a (virtual or physical) data warehouse or data mart, where it can then be modeled and analyzed before being presented to the user. The creation of such data marts can significantly improve performance. For example, data marts can store aggregate tables, which are physical tables that store aggregates of measures across multiple levels of a hierarchy. These aggregate tables can be thought of as pre-computed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. The persistence of such aggregates can improve query performance by providing faster access to specific data than would otherwise be achieved by executing full queries to gather the underlying data. In other words, by pre-computing and storing the desired aggregates ahead of time, query response time is reduced since the server no longer needs to compute and gather all of the results upon each execution of the query.
However, many shortcomings still exist in the creation and automation of such data marts and aggregates. The task of manually creating aggregate tables is tedious, usually requiring writing complicated data definition language (DDL) and/or data manipulation language (DML) to create tables in the databases involved. Additionally, these tables need to be mapped into the repository metadata to be available for queries. This is a time consuming, and a possibly error prone process. Additionally, as the number of aggregate tables increases, taking advantage of such aggregates and putting them to good use becomes more and more difficult. In light of these inefficiencies, what is desirable is a way to automate, to the greatest extent, the creation of aggregate tables and their mappings into the metadata.